package com.chen.display.controller;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.RestController;
import java.io.*;
import java.util.*;

/**
 *  maven需导入poi包
 * 文件放的位置为C:\Users\Administrator\Desktop\a.xlsx   可自己指定
 */
@RestController
public class JavaExcel {
    static XSSFRow row;
    ArrayList<ArrayList<String>> list2 = new ArrayList<>();

    public static void main(String[] args)throws Exception
    {
        JavaExcel javaExcel = new JavaExcel();
        javaExcel.input();
        System.out.println(javaExcel.list2);
        javaExcel.out();
    }

    /**
     * 从原excel表中取出数据并存入列表中。 ps： list1格式：[省, 市, 区]   list2格式：[[省, 市, 区], [河南省, 郑州市, 二七区], [江苏省, 南京市, 秦淮区]]
     */
    public void input() throws IOException{
        FileInputStream f = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\a.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(f);
        XSSFSheet spreadSheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = spreadSheet.iterator();
        while(rowIterator.hasNext()){
            row = (XSSFRow)rowIterator.next();
            ArrayList<String> list1 = new ArrayList<>();
            for (int i=0;i<3;i++){
                XSSFCell cell = row.getCell(i);
                String str = cell.getStringCellValue();
                list1.add(str);
            }
            System.out.println(list1);
            list2.add(list1);
           /* Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getCellType()){
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.println(cell.getNumericCellValue()+"\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.println(cell.getStringCellValue()+"\t\t");
                        break;
                }
            }
            System.out.println();*/
        }
        f.close();

    }

    /**
     * 将存入list2中的数据存储到目标excel中
     */
    public void out() throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet spreadSheet = workbook.createSheet("sheetName");
        int rowId=0;
        for(ArrayList<String> l : list2){
            row = spreadSheet.createRow(rowId++);
            int cellId = 0;
            Cell cell = row.createCell(cellId);
            String str = "";
            int count=0;
            for (String s : l){
                count++;
                if(count<l.size()){
                    str += s+",";
                } else {
                    str += s;
                }
            }
            cell.setCellValue(str);
        }
        FileOutputStream out = new FileOutputStream(
                new File("C:\\Users\\Administrator\\Desktop\\abc.xlsx"));

        workbook.write(out);
        out.close();
        System.out.println("createworkbook.xlsx written successfully");
    }













/*


//    @PostMapping("/update")
    public void update() throws IOException {
//        String path = "C:\\Users\\Administrator\\Desktop\\a.xlsx";
        File f = new File("C:\\Users\\Administrator\\Desktop\\a.xlsx");
        InputStream inputStream = new FileInputStream(f);
        HSSFWorkbook xssfWorkbook = new HSSFWorkbook(inputStream);
//        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        HSSFSheet sheet1 = xssfWorkbook.getSheetAt(0);
        for (Row row : sheet1){
            for (Cell hssfCell : row){
                if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN);
            }
        }



    }
*/
}

